QueryDef Object, QueryDefs Collection Example

This example creates a new QueryDef object and appends it to the QueryDefs collection of the Northwind Database object. It then enumerates the QueryDefs collection and the Properties collection of the new QueryDef.

Sub QueryDefX()

    Dim dbsNorthwind As Database
    Dim qdfNew As QueryDef
    Dim qdfLoop As QueryDef
    Dim prpLoop As Property

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")

    ' Create new QueryDef object. Because it has a 
    ' name, it is automatically appended to the 
    ' QueryDefs collection.
    Set qdfNew = dbsNorthwind.CreateQueryDef("NewQueryDef", _
            "SELECT * FROM Categories")

    With dbsNorthwind
        Debug.Print .QueryDefs.Count & _
            " QueryDefs in " & .Name

        ' Enumerate QueryDefs collection.
        For Each qdfLoop In .QueryDefs
            Debug.Print "  " & qdfLoop.Name
        Next qdfLoop

        With qdfNew
            Debug.Print "Properties of " & .Name

            ' Enumerate Properties collection of new 
            ' QueryDef object.
            For Each prpLoop In .Properties
                On Error Resume Next
                Debug.Print "  " & prpLoop.Name & " - " & _
                    IIf(prpLoop = "", "[empty]", prpLoop)
                On Error Goto 0
            Next prpLoop
        End With

        ' Delete new QueryDef because this is a 
        ' demonstration.
        .QueryDefs.Delete qdfNew.Name
        .Close
    End With

End Sub